﻿using System;
using SmartSiteManagementSystem.IRepository.Material;
using SmartSiteManagementSystem.Models.Material;
using SmartSiteManagementSystem.Repository.DBHelper;
using System.Data;
using Dapper;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using MySql.Data.MySqlClient;

namespace SmartSiteManagementSystem.Repository.Material
{
    public class MaterialCatalogInfoRepository : IMaterialCatalogInfoRepository
    //,IDisposable
    {
        #region dataTable
        /*
create table `material_catalog`( 
	`catalog_id` int(11) not null AUTO_INCREMENT primary key, 
	`catalog_name` varchar(50) not null ,
	`parent_id` int not null,
	`sort_order` int not null,
	`remark` varchar(250) null,
    `is_parent`  bit not null default 0,
    `is_stop`  bit not null default 0,
	`is_deleted`  bit not null default 0,
	`create_time` datetime not null default now(),
	`update_time` datetime not null default now()
    ) default charset=utf8;
         */
        #endregion
        //private IDbConnection _readConn;
        //private IDbConnection _writeConn;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="catalogId"></param>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public bool Delete(int catalogId, int parentId)
        {
            bool back = false;
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {
                _writeConn.Open();
                using (IDbTransaction transaction = _writeConn.BeginTransaction())
                {
                    try
                    {
                        #region deleteMaterialCatalog

                        string deleteMaterialCatalogSql = "delete from material_catalog where catalog_id=@catalog_id";
                        DynamicParameters deleteMaterialCatalogSqlPars = new DynamicParameters();
                        deleteMaterialCatalogSqlPars.Add("catalog_id", catalogId);
                        int result = _writeConn.Execute(deleteMaterialCatalogSql, deleteMaterialCatalogSqlPars, transaction);
                        #endregion
                        if (result > 0 && parentId > 0)
                        {
                            Hashtable param = new Hashtable();
                            param.Add("parent_id", parentId);
                            param.Add("is_deleted", 0);
                            if (GetCount(param) == 0)
                            {
                                #region UpdateFatherMaterialCatalog
                                string UpdateFatherMaterialCatalogSql = @"UPDATE material_catalog
SET is_parent= 0
WHERE catalog_id = @ParentId;";
                                _writeConn.Execute(UpdateFatherMaterialCatalogSql, new
                                {
                                    ParentId = parentId
                                }, transaction);
                                #endregion
                            }
                        }
                        transaction.Commit();
                        back = true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        back = false;
                    }

                }
            }
            return back;
        }





        public bool Delete(Hashtable param)
        {
            bool back = false;
            if (param == null)
                param = new Hashtable();
            String querySql = @"delete from material_catalog";
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where ");
                    int parCount = 0;
                    if (param.ContainsKey("catalog_id"))
                    {
                        pars.Add("catalog_id", param["catalog_id"]);

                        whereCondition.Append(" catalog_id =@catalog_id");
                        parCount++;
                    }
                    if (param.ContainsKey("catalog_name"))
                    {
                        pars.Add("catalog_name", param["catalog_name"]);
                        if(parCount>0)
                        {
                            whereCondition.Append(" and ");
                        }
                        whereCondition.Append("catalog_name =@catalog_name");
                        parCount++;
                    }
                    if (param.ContainsKey("parent_id"))
                    {
                        pars.Add("parent_id", param["parent_id"]);
                        if (parCount > 0)
                        {
                            whereCondition.Append(" and ");
                        }
                        whereCondition.Append("parent_id =@parent_id");
                        parCount++;
                    }
                    if (param.ContainsKey("is_deleted"))
                    {
                        pars.Add("is_deleted", param["is_deleted"]);
                        if (parCount > 0)
                        {
                            whereCondition.Append(" and ");
                        }
                        whereCondition.Append("is_deleted =@is_deleted");
                        parCount++;
                    }
                }
                string execSql = querySql + whereCondition.ToString();
                int backInt = _writeConn.Execute(execSql, pars);
                if(backInt>0)
                {
                    back = true;
                }
            }
            return back;
        }

        public MaterialCatalogInfo Get(Hashtable param)
        {
            MaterialCatalogInfo back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"SELECT catalog_id as CatalogId,catalog_name as CatalogName,parent_id as ParentId,sort_order as SortOrder,remark as Remark,is_parent as IsParent,is_deleted as IsDeleted,is_stop as IsStop, create_time as CreateTime,update_time as UpdateTime FROM material_catalog";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {

                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("catalog_id"))
                    {
                        pars.Add("catalog_id", param["catalog_id"]);
                        whereCondition.Append(" and catalog_id =@catalog_id");
                    }
                    if (param.ContainsKey("catalog_name"))
                    {
                        pars.Add("catalog_name", param["catalog_name"]);
                        whereCondition.Append(" and catalog_name =@catalog_name");
                    }
                    if (param.ContainsKey("parent_id"))
                    {
                        pars.Add("parent_id", param["parent_id"]);
                        whereCondition.Append(" and parent_id =@parent_id");
                    }
                    if (param.ContainsKey("is_stop"))
                    {
                        pars.Add("is_stop", param["is_stop"]);
                        whereCondition.Append(" and is_stop =@is_stop");
                    }
                    if (param.ContainsKey("is_deleted"))
                    {
                        pars.Add("is_deleted", param["is_deleted"]);
                        whereCondition.Append(" and is_deleted =@is_deleted");
                    }
                }
                whereCondition.Append(" limit 1");
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.QueryFirstOrDefault<MaterialCatalogInfo>(execSql, pars);
            }
            return back;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetCount(Hashtable param)
        {
            int back = 0;
            if (param == null)
                param = new Hashtable();
            String querySql = "select count(catalog_id) from material_catalog";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("catalog_id"))
                    {
                        pars.Add("catalog_id", param["catalog_id"]);
                        whereCondition.Append(" and catalog_id =@catalog_id");
                    }
                    if (param.ContainsKey("catalog_name"))
                    {
                        pars.Add("catalog_name", param["catalog_name"]);
                        whereCondition.Append(" and catalog_name =@catalog_name");
                    }
                    if (param.ContainsKey("parent_id"))
                    {
                        pars.Add("parent_id", param["parent_id"]);
                        whereCondition.Append(" and parent_id =@parent_id");
                    }
                    if (param.ContainsKey("is_stop"))
                    {
                        pars.Add("is_stop", param["is_stop"]);
                        whereCondition.Append(" and is_stop =@is_stop");
                    }
                    if (param.ContainsKey("is_deleted"))
                    {
                        pars.Add("is_deleted", param["is_deleted"]);
                        whereCondition.Append(" and is_deleted =@is_deleted");
                    }

                    
                }
                
                string execSql = querySql + whereCondition.ToString();
                #endregion
                back = _readConn.QueryFirstOrDefault<int>(execSql, pars);
            }
            return back;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public IList<MaterialCatalogInfo> GetList(Hashtable param)
        {
            IList<MaterialCatalogInfo> back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"SELECT catalog_id as CatalogId,catalog_name as CatalogName,parent_id as ParentId,sort_order as SortOrder,remark as Remark,is_parent as IsParent,is_deleted as IsDeleted,is_stop as IsStop,create_time as CreateTime,update_time as UpdateTime FROM material_catalog";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("catalog_id"))
                    {
                        pars.Add("catalog_id", param["catalog_id"]);
                        whereCondition.Append(" and catalog_id =@catalog_id");
                    }
                    if (param.ContainsKey("catalog_name"))
                    {
                        pars.Add("catalog_name", param["catalog_name"]);
                        whereCondition.Append(" and catalog_name =@catalog_name");
                    }
                    if (param.ContainsKey("parent_id"))
                    {
                        pars.Add("parent_id", param["parent_id"]);
                        whereCondition.Append(" and parent_id =@parent_id");
                    }
                    if (param.ContainsKey("is_stop"))
                    {
                        pars.Add("is_stop", param["is_stop"]);
                        whereCondition.Append(" and is_stop =@is_stop");
                    }
                    if (param.ContainsKey("is_deleted"))
                    {
                        pars.Add("is_deleted", param["is_deleted"]);
                        whereCondition.Append(" and is_deleted =@is_deleted");
                    }
                    if (param.ContainsKey("ParentIds"))
                    {
                        pars.Add("ParentIds", param["ParentIds"]);
                        whereCondition.Append(" and parent_id in (@ParentIds)");
                    }
                    if (param.ContainsKey("CatalogIds"))
                    {
                        pars.Add("CatalogIds", param["CatalogIds"]);
                        whereCondition.Append(" and catalog_id in(@CatalogIds)");
                    }
                }
                #endregion
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.Query<MaterialCatalogInfo>(execSql, pars).AsList<MaterialCatalogInfo>();
            }
            return back;
        }




        /// <summary>
        /// 
        /// </summary>
        /// <param name="param"></param>
        /// <param name="orderBy"></param>
        /// <param name="first"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public IList<MaterialCatalogInfo> GetList(Hashtable param, string orderBy, int first, int rows)
        {
            IList<MaterialCatalogInfo> back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"SELECT catalog_id as CatalogId,catalog_name as CatalogName,parent_id as ParentId,sort_order as SortOrder,remark as Remark,is_parent as IsParent,is_deleted as IsDeleted,is_stop as IsStop,create_time as CreateTime,update_time as UpdateTime FROM material_catalog";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("catalog_id"))
                    {
                        pars.Add("catalog_id", param["catalog_id"]);
                        whereCondition.Append(" and catalog_id =@catalog_id");
                    }
                    if (param.ContainsKey("catalog_name"))
                    {
                        pars.Add("catalog_name", param["catalog_name"]);
                        whereCondition.Append(" and catalog_name =@catalog_name");
                    }
                    if (param.ContainsKey("parent_id"))
                    {
                        pars.Add("parent_id", param["parent_id"]);
                        whereCondition.Append(" and parent_id =@parent_id");
                    }
                    if (param.ContainsKey("is_stop"))
                    {
                        pars.Add("is_stop", param["is_stop"]);
                        whereCondition.Append(" and is_stop =@is_stop");
                    }
                    if (param.ContainsKey("is_deleted"))
                    {
                        pars.Add("is_deleted", param["is_deleted"]);
                        whereCondition.Append(" and is_deleted =@is_deleted");
                    }
                    if (param.ContainsKey("ParentIds"))
                    {
                        pars.Add("ParentIds", param["ParentIds"]);
                        whereCondition.Append(" and parent_id in (@ParentIds)");
                    }
                    if (param.ContainsKey("CatalogIds"))
                    {
                        pars.Add("CatalogIds", param["CatalogIds"]);
                        whereCondition.Append(" and catalog_id in(@CatalogIds)");
                    }
                }
                if (string.IsNullOrEmpty(orderBy))
                {
                    whereCondition.Append(" order by catalog_id desc");
                }
                else
                {
                    whereCondition.Append(" order by @orderBy");
                    pars.Add("orderBy", orderBy);
                }
                whereCondition.Append(" limit @first,@rows");
                pars.Add("first", first);
                pars.Add("rows", rows);
                #endregion
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.Query<MaterialCatalogInfo>(execSql, pars).AsList<MaterialCatalogInfo>();
            }
            return back;
        }




        /// <summary>
        /// 
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public IList<MaterialCatalogInfo> GetParents(Hashtable param)
        {
            IList<MaterialCatalogInfo> back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"SELECT gc.catalog_id as CatalogId, gc.parent_id as ParentId, gc.is_stop as IsStop, gc.is_deleted as IsDeleted FROM material_catalog gc LEFT JOIN material_catalog gc1  ON gc1.parent_id= gc.catalog_id LEFT JOIN material_catalog gc2  ON gc2.parent_id= gc1.catalog_id  ";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    if (param.ContainsKey("CatalogId"))
                    {
                        pars.Add("CatalogId", param["CatalogId"]);
                        whereCondition.Append(" WHERE gc.catalog_id=@CatalogId OR  gc1.catalog_id=@CatalogId OR gc2.catalog_id=@CatalogId");
                    }
                }

                #endregion
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.Query<MaterialCatalogInfo>(execSql, pars).AsList<MaterialCatalogInfo>();
            }
            return back;
        }
        




        /// <summary>
        /// 
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int Insert(MaterialCatalogInfo obj)
        {
            int back = 0;
            #region sql
            string sql = @"INSERT INTO material_catalog
(catalog_name,
parent_id,
sort_order,
remark,
is_parent,
is_stop,
is_deleted,
create_time,
update_time)
VALUES
(@catalog_name,
@parent_id,
@sort_order,
@remark,
@is_parent,
@is_stop,
@is_deleted,
@create_time,
@update_time);select last_insert_id();";
            #endregion
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

                back = _writeConn.QueryFirst<int>(sql, new
                {
                    catalog_name=obj.CatalogName,
                    parent_id = obj.ParentId,
                    sort_order = obj.SortOrder,
                    remark = obj.Remark,
                    is_parent= obj.IsParent,
                    is_stop =obj.IsStop,
                    is_deleted = obj.IsDeleted,
                    create_time = obj.CreateTime,
                    update_time = obj.UpdateTime
                });
            }
            return back;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool Update(MaterialCatalogInfo obj)
        {
            bool back = false;
            #region sql
            string sql = @"UPDATE material_catalog
SET
catalog_name = @catalog_name,
parent_id = @parent_id,
sort_order = @sort_order,
remark =@remark,
is_parent =@is_parent,
is_stop = @is_stop,
is_deleted = @is_deleted,
create_time = @create_time,
update_time = @update_time
WHERE catalog_id = @catalog_id;";
            #endregion
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

               int backint = _writeConn.Execute(sql, new
                {
                   catalog_id=obj.CatalogId,
                   catalog_name = obj.CatalogName,
                   parent_id = obj.ParentId,
                   sort_order = obj.SortOrder,
                   remark = obj.Remark,
                   is_parent = obj.IsParent,
                   is_stop = obj.IsStop,
                   is_deleted = obj.IsDeleted,
                   create_time = obj.CreateTime,
                   update_time = obj.UpdateTime
               });
                if(backint>0)
                {
                    back = true;
                }
            }
            return back;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public bool Update(Hashtable param)
        {
            bool back = false;
            #region
            DynamicParameters pars = new DynamicParameters();
            StringBuilder setCondition = new StringBuilder();
            if (param.Count > 0&& param.ContainsKey("catalog_id"))
            {
                int parCount = 0;
                string updateSql = @"UPDATE material_catalog ";
                setCondition.Append(" SET ");
                if (param.ContainsKey("catalog_name"))
                {
                    pars.Add("catalog_name", param["catalog_name"]);
                    if(parCount>0)
                    {
                        setCondition.Append(",");
                    }
                    setCondition.Append("catalog_name =@catalog_name");
                    parCount++;
                }
                if (param.ContainsKey("parent_id"))
                {
                    pars.Add("parent_id", param["parent_id"]);
                    if (parCount > 0)
                    {
                        setCondition.Append(",");
                    }
                    setCondition.Append("parent_id =@parent_id");
                    parCount++;
                }
                if (param.ContainsKey("sort_order"))
                {
                    pars.Add("sort_order", param["sort_order"]);
                    if (parCount > 0)
                    {
                        setCondition.Append(",");
                    }
                    setCondition.Append("sort_order =@sort_order");
                    parCount++;
                }

                if (param.ContainsKey("remark"))
                {
                    pars.Add("remark", param["remark"]);
                    if (parCount > 0)
                    {
                        setCondition.Append(",");
                    }
                    setCondition.Append("remark =@remark");
                    parCount++;
                }
                if (param.ContainsKey("is_parent"))
                {
                    pars.Add("is_parent", param["is_parent"]);
                    if (parCount > 0)
                    {
                        setCondition.Append(",");
                    }
                    setCondition.Append("is_parent =@is_parent");
                    parCount++;
                }
                if (param.ContainsKey("is_stop"))
                {
                    pars.Add("is_stop", param["is_stop"]);
                    if (parCount > 0)
                    {
                        setCondition.Append(",");
                    }
                    setCondition.Append("is_stop =@is_stop");
                    parCount++;
                }
                if (param.ContainsKey("is_deleted"))
                {
                    pars.Add("is_deleted", param["is_deleted"]);
                    if (parCount > 0)
                    {
                        setCondition.Append(",");
                    }
                    setCondition.Append("is_deleted =@is_deleted");
                    parCount++;
                }
                if (param.ContainsKey("create_time"))
                {
                    pars.Add("create_time", param["create_time"]);
                    if (parCount > 0)
                    {
                        setCondition.Append(",");
                    }
                    setCondition.Append("create_time =@create_time");
                    parCount++;
                }
                if (param.ContainsKey("update_time"))
                {
                    pars.Add("update_time", param["update_time"]);
                    if (parCount > 0)
                    {
                        setCondition.Append(",");
                    }
                    setCondition.Append("update_time =@update_time");
                    parCount++;
                }
                pars.Add("catalog_id", param["catalog_id"]);
                setCondition.Append(" WHERE catalog_id = @catalog_id;");
                string execSql = updateSql + setCondition.ToString();
                using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
                {

                    int backint = _writeConn.Execute(execSql, pars);
                    if (backint > 0)
                    {
                        back = true;
                    }
                }
            }
            #endregion
            return back;
        }






    }
}

